package com.hfjyz.registration.dao;

import java.math.BigInteger;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.hfjyz.registration.model.Admission;
import com.hfjyz.registration.model.Room;
import com.hfjyz.registration.model.Seat;
import com.hfjyz.registration.object.PrintInfoBean;

public class AdmissionDaoImpl extends BaseDaoImpl<Admission, Integer> implements AdmissionDao {
    protected AdmissionDaoImpl() {
        super(Admission.class);
    }

    @Override
    public Admission getByUid(Integer userId, Integer examId) throws Exception {
        String hql = "from Admission A, Seat S, Room R where A.userId=S.userId and R.id=S.roomId and A.userId=? and A.examId = ?";
        List<?> list = hibernateTemplate.find(hql, new Object[] { userId, examId });
        if (list == null || list.isEmpty()) {
            return null;
        }
        Object obj = list.get(0);
        Object[] o = (Object[]) obj;
        Admission admission = (Admission) o[0];
        Seat seat = (Seat) o[1];
        Room room = (Room) o[2];
        seat.setRoom(room);
        admission.setSeat(seat);

        return admission;
    }

    @SuppressWarnings({ "unchecked" })
    @Override
    public void deleteByUids(final List<Integer> uids, final Integer examId) throws Exception {

        List<Admission> ads = (List<Admission>) hibernateTemplate.execute(new HibernateCallback() {
            @Override
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                return session.createQuery("from Admission where userId in (:uids) and examId = :eid ")
                        .setParameter("eid", examId).setParameterList("uids", uids).list();
            }
        });
        for (Admission ad : ads) {
            hibernateTemplate.delete(ad);
        }
        hibernateTemplate.flush();
    }

    @Override
    public Admission get(String idCardNo, String admissionCode, Integer examId) throws Exception {
        String hql = "from Admission A, User U where A.userId=U.id and U.idCardNo=? and A.code=? and A.examId = ?";
        Object[] params = new Object[] { idCardNo, admissionCode, examId };
        List<?> list = hibernateTemplate.find(hql, params);
        if (list == null || list.isEmpty()) {
            return null;
        }
        Object obj = list.get(0);
        Object[] o = (Object[]) obj;
        Admission admission = (Admission) o[0];
        return admission;
    }

    @Override
    public List<PrintInfoBean> getPrintInfo(final Integer examId) {
        final String strSQL = "select * from ( select  "
                + " 	d.name as departName, "
                + " 	o.code as officeCode, "
                + " 	o.name as officeName, "
                + " 	(select count(ad.printFlg) from Apply a, Admission ad where a.officeId = o.id and ad.userId = a.userId and ad.examId = o.examId and ad.printFlg = 0) as nonPrint, "
                + " 	(select count(ad.printFlg) from Apply a, Admission ad where a.officeId = o.id and ad.userId = a.userId and ad.examId = o.examId and ad.printFlg = 1) as Printed "
                + " from " + " 	Office o " + " 	join depart d on d.id = o.departId " + " where o.examId = ? ) office ";

        List<?> listAll = hibernateTemplate.executeFind(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                SQLQuery query = session.createSQLQuery(strSQL);
                query.setInteger(0, examId);
                return query.list();
            }
        });
        List<PrintInfoBean> beanList = new ArrayList<PrintInfoBean>();
        for (Object obj : listAll) {
            PrintInfoBean bean = new PrintInfoBean();
            Object[] results = (Object[]) obj;
            bean.setDeptName((String) results[0]);
            bean.setPostCode(String.valueOf(results[1]));
            bean.setPostName((String) results[2]);
            bean.setNotPrintedCount(((BigInteger) results[3]).intValue());
            bean.setPrintedCount(((BigInteger) results[4]).intValue());
            beanList.add(bean);
        }

        return beanList;
    }

    @Override
    public List<Admission> getAllByExamId(Integer examId) throws Exception {
        String hql = "from Admission A where A.examId = ?";
        Object[] params = new Object[] { examId };
        List<?> list = hibernateTemplate.find(hql, params);
        if (list == null || list.isEmpty()) {
            return new ArrayList<Admission>(0);
        }
        List<Admission> admissions = new ArrayList<Admission>();
        for (Object row : list) {
            admissions.add((Admission)row);
        }
        return admissions;
    }
}
